﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace AuriaCompte.Lib.Dao
{
    public class DaoMouvement
        : DaoBase
    {
        public DaoMouvement()
            : base()
        {
            this.TableName = "mouvement";
            this.ViewName = "mouvement";
            this.PrimaryKeyName = "i_mouvement_id";
            this.ListColonne.Add("i_mouvement_id");
            this.ListColonne.Add("d_date");
            this.ListColonne.Add("d_date_pointage");
            this.ListColonne.Add("r_montant_debit");
            this.ListColonne.Add("r_montant_credit");
            this.ListColonne.Add("i_paiement_fk");
            this.ListColonne.Add("i_categorie_fk");
            this.ListColonne.Add("i_compte_fk");
            this.ListColonne.Add("s_reference");
        }

        public Mouvement SelectById(Int32 idP)
        {
            lParam.Clear();
            Mouvement retour = new Mouvement();
            String req = this.DebutSelect
                + this.TableName
                + " WHERE " + this.PrimaryKeyName + " = @Id";

            lParam.Add(new SQLiteParameter("@Id", idP));

            SQLiteDataReader dr = this.ExecuteQueryReader(req, lParam);

            if (dr.Read())
                retour = ChargeFromReader(dr);
            dr.Close();
            return retour;
        }

        public List<Mouvement> SelectAll()
        {
            lParam.Clear();
            List<Mouvement> retour = new List<Mouvement>();
            String req = this.DebutSelect
                + this.TableName;

            SQLiteDataReader dr = this.ExecuteQueryReader(req);

            while (dr.Read())
                retour.Add(ChargeFromReader(dr));
            dr.Close();
            return retour;
        }

        public Mouvement Insert(Mouvement p)
        {
            lParam.Clear();

            Mouvement retour = new Mouvement();
            String req = "INSERT INTO " + TableName + "(i_mouvement_id, s_libelle, d_date, d_date_pointage, r_montant_debit, r_montant_credit, i_paiement_fk, i_categorie_fk, i_compte_fk, s_reference) " +
                "VALUES(NULL, @s_libelle, @d_date, @d_date_pointage, @r_montant_debit, @r_montant_credit, @i_paiement_fk, @i_categorie_fk, @i_compte_fk, @s_reference)";

            lParam.Add(new SQLiteParameter("@s_libelle", p.s_libelle));
            lParam.Add(new SQLiteParameter("@d_date", p.d_date));
            lParam.Add(new SQLiteParameter("@d_date_pointage", p.d_date_pointage));
            lParam.Add(new SQLiteParameter("@r_montant_debit", p.r_montant_debit));
            lParam.Add(new SQLiteParameter("@r_montant_credit", p.r_montant_credit));
            lParam.Add(new SQLiteParameter("@i_paiement_fk", p.i_paiement_fk));
            lParam.Add(new SQLiteParameter("@i_categorie_fk", p.i_categorie_fk));
            lParam.Add(new SQLiteParameter("@i_compte_fk", p.i_compte_fk));
            lParam.Add(new SQLiteParameter("@s_reference", p.s_reference));
            
            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(Convert.ToInt32(this.LastInsertId));

            return retour;
        }

        public Mouvement Update(Mouvement m)
        {
            lParam.Clear();

            Mouvement retour = new Mouvement();
            String req = "UPDATE " + TableName + " SET s_libelle=@s_libelle, d_date=@d_date, d_date_pointage=@d_date_pointage, r_montant_debit=@r_montant_debit, r_montant_credit=@r_montant_credit, i_paiement_fk=@i_paiement_fk, i_categorie_fk=@i_categorie_fk, i_compte_fk=@i_compte_fk, s_reference=@s_reference " +
                " WHERE i_mouvement_id=@i_mouvement_id";

            lParam.Add(new SQLiteParameter("@i_mouvement_id", m.i_mouvement_id));
            lParam.Add(new SQLiteParameter("@s_libelle", m.s_libelle));
            lParam.Add(new SQLiteParameter("@d_date", m.d_date));
            lParam.Add(new SQLiteParameter("@d_date_pointage", m.d_date_pointage));
            lParam.Add(new SQLiteParameter("@r_montant_debit", m.r_montant_debit));
            lParam.Add(new SQLiteParameter("@r_montant_credit", m.r_montant_credit));
            lParam.Add(new SQLiteParameter("@i_paiement_fk", m.i_paiement_fk));
            lParam.Add(new SQLiteParameter("@i_categorie_fk", m.i_categorie_fk));
            lParam.Add(new SQLiteParameter("@i_compte_fk", m.i_compte_fk));
            lParam.Add(new SQLiteParameter("@s_reference", m.s_reference));

            
            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(m.i_mouvement_id);

            return retour;
        }

        private Mouvement ChargeFromReader(SQLiteDataReader dr)
        {
            Mouvement retour = new Mouvement();

            retour.i_mouvement_id = dr.GetInt32(0);
            if (dr.IsDBNull(1))
                retour.s_libelle = String.Empty;
            else
                retour.s_libelle = dr.GetString(1);
            if (dr.IsDBNull(2))
                retour.d_date = DateTime.Now;
            else
                retour.d_date = dr.GetDateTime(2);
            if (dr.IsDBNull(3))
                retour.d_date_pointage = null;
            else
                retour.d_date_pointage = dr.GetDateTime(3);
            if (dr.IsDBNull(4))
                retour.r_montant_debit = 0.0;
            else
                retour.r_montant_debit = dr.GetDouble(4);
            if (dr.IsDBNull(5))
                retour.r_montant_credit = 0.0;
            else
                retour.r_montant_credit = dr.GetDouble(5);
            if (dr.IsDBNull(6))
                retour.i_paiement_fk = 0;
            else
                retour.i_paiement_fk = dr.GetInt32(6);
            if (dr.IsDBNull(7))
                retour.i_categorie_fk = 0;
            else
                retour.i_categorie_fk = dr.GetInt32(7);
            if (dr.IsDBNull(8))
                retour.i_compte_fk = 0;
            else
                retour.i_compte_fk = dr.GetInt32(8);
            if (dr.IsDBNull(9))
                retour.s_reference = String.Empty;
            else
                retour.s_reference = dr.GetString(9);

            return retour;
        }
    }
}
